﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;

namespace DAO_Layer
{
    class MysqlDaoUsuario : DAO_Usuarios
    {
        private String TableName = "usuarios";
        Sqlconnector mysqlconn;

        public MysqlDaoUsuario()
        {
            mysqlconn = new Sqlconnector();
        }

        public void add(Model.Usuario u, int rol_id)
        {
            mysqlconn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = mysqlconn.Mysqlconnection;
            comm.CommandText =
                    "INSERT INTO " + TableName + "(username,password,email,id_rol) " +
                    "VALUES (@username,@password,@email,@id_rol)";

            comm.Parameters.AddWithValue("@username", u.Username);
            comm.Parameters.AddWithValue("@password", u.Password);
            comm.Parameters.AddWithValue("@email", u.Email);
            comm.Parameters.AddWithValue("@id_rol", rol_id);

            try
            {
                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.ToString());
            }
            

            mysqlconn.Close();
        }

        public bool validUser(string u)
        {

            mysqlconn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = mysqlconn.Mysqlconnection;
            comm.CommandText = "SELECT id FROM " + TableName + " WHERE username=@username";
            comm.Parameters.AddWithValue("@username", u);

            MySqlDataReader myReader = null;

            myReader = comm.ExecuteReader();

            bool hayFilas = myReader.HasRows;
            
            myReader.Close();
            mysqlconn.Close();

            return !hayFilas;
        }

        public bool validEmail(string e)
        {
            mysqlconn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = mysqlconn.Mysqlconnection;
            comm.CommandText = "SELECT email FROM " + TableName + " WHERE email=@email";
            comm.Parameters.AddWithValue("@email", e);

            MySqlDataReader myReader = null;
            try
            {
                myReader = comm.ExecuteReader();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            
            bool hayFilas = myReader.HasRows;

            myReader.Close();
            mysqlconn.Close();

            return !hayFilas;
        }

        public Usuario validCredentials(Usuario u) 
        {
            Usuario returnUser = null;

            u.hashPassword();

            mysqlconn.Open();

            MySqlCommand comm = new MySqlCommand();
            comm.Connection = mysqlconn.Mysqlconnection;
            comm.CommandText = "SELECT " + TableName + ".id AS  userid ,  username ,  password , email ,  roles.nombre ,  roles.descripcion ,  roles.id AS  rolid  FROM " + TableName + " INNER JOIN roles ON " + TableName + ".id_rol = roles.id  WHERE password=@password AND username=@username";
            comm.Parameters.AddWithValue("@password", u.Password);
            comm.Parameters.AddWithValue("@username", u.Username);

            MySqlDataReader myReader = null;
            try
            {
                myReader = comm.ExecuteReader();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            if (myReader.Read())
            {
                int id = Int32.Parse(myReader["userid"].ToString());
                String username = myReader["username"].ToString();
                String password = myReader["password"].ToString();
                String email = myReader["email"].ToString();

                returnUser = new Usuario(id, username, password, email);
                returnUser.Rol = new Rol();
                returnUser.Rol.Id = Int32.Parse(myReader["rolid"].ToString());
                returnUser.Rol.Nombre = myReader["nombre"].ToString();
                returnUser.Rol.Descripcion = myReader["descripcion"].ToString();
            }


            myReader.Close();
            mysqlconn.Close();

            return returnUser;

        }


        public Usuario getUserbyEmail(String e)
        {
            mysqlconn.Open();
            Usuario returnUser = null;
            MySqlCommand comm = new MySqlCommand();
            comm.Connection = mysqlconn.Mysqlconnection;
            comm.CommandText = "SELECT id,username,password,email FROM " + TableName + " WHERE email=@email";
            comm.Parameters.AddWithValue("@email", e);

            MySqlDataReader myReader = null;
            try
            {
                myReader = comm.ExecuteReader();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            if (myReader.Read())
            {
                int id = Int32.Parse(myReader["id"].ToString());
                String username = myReader["username"].ToString();
                String password = myReader["password"].ToString();
                String email = myReader["email"].ToString();

                returnUser = new Usuario(id, username, password, email);
      
            }

            myReader.Close();
            mysqlconn.Close();

            return returnUser;
        }
    }
}
